﻿
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Sql;
    using System.Data.SqlClient;
    using System.Diagnostics;
    using System.Drawing;
    using System.Drawing.Imaging;
    using System.IO;
    using System.Text;
    using System.Web;

    using Pub;

    using Raize.CodeSiteLogging;

namespace Pub
{
    public class AdoMSSQL : IADO
    {
        #region Fields

        public static String S_Con;

        public String access_con;
        public SqlCommand cm = new SqlCommand();
        public SqlConnection cn = new SqlConnection();
        public SqlDataAdapter da;
        public DataSet ds;
        public int ds_count = 0;
        public SqlParameter[] pars;
        public List<SqlParameter> pars2;
        public String sqlServer_con;
        public String s_sql = null;

        #endregion Fields

        #region Constructors

        public AdoMSSQL()
        {
            OpenDB();
        }

        #endregion Constructors

        #region Methods

        public Boolean CloseDB()
        {
            try
            {
                cn.Close();
                return true;
            }
            catch (Exception ex)
            {
                CodeSite.Send("AdoMSSQL.cs CloseDB:", ex);
                return false;
            }
        }

        /// <summary>
        /// 执行非select语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int cmd1(String sql)
        {
            try
            {
                cm = new SqlCommand(sql, cn);
                return cm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                CodeSite.Send(sql + "; " + ex.ToString());
                CodeSite.Send("AdoMSSQL.cs cmd1:", ex);
                return 0;
            }
        }

        public int cmd1(String sql, List<SqlParameter> pars)
        {
            if (pars == null) return 0;
            SqlParameter[] pars2 = new SqlParameter[pars.Count];
            for (int i = 0; i < pars.Count; i++) pars2[i] = pars[i];

            cm.Connection = cn;
            cm.CommandText = sql;
            cm.Parameters.Clear();
            cm.Parameters.AddRange(pars2);
            cm.CommandType = CommandType.StoredProcedure;
            return cm.ExecuteNonQuery();
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int cmd2(String sql)
        {
            if (pars == null) return 0;

            cm.Connection = cn;
            cm.CommandText = sql;
            cm.Parameters.Clear();
            cm.Parameters.AddRange(pars);
            cm.CommandType = CommandType.StoredProcedure;
            return cm.ExecuteNonQuery();
        }

        public int cmd2(String sql, SqlParameter[] pars)
        {
            try
            {
                if (pars == null) return 0;

                cm.Connection = cn;
                cm.CommandText = sql;
                cm.Parameters.Clear();
                cm.Parameters.AddRange(pars);
                cm.CommandType = CommandType.StoredProcedure;
                return cm.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                CodeSite.Send(ex.ToString());
                return 0;
            }
        }

        public Int32 getNewID(string sTab, string sCol)
        {
            string sql = "select Isnull(Max(Convert(int," + sCol + ")),0) + 1 as s1 from " + sTab;
            SqlDataAdapter da = new SqlDataAdapter(sql, cn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            if (CPub.dsIsNull(ds)) return 0;
            else return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
        }

        public Boolean OpenDB()
        {
            S_Con = CConfig.getPermite("DBCon");
            //S_Con = CConfig.getConStr(0);
            //CodeSite.Send(S_Con);
            cn = new SqlConnection(S_Con);
            try
            {
                cn.Open();
                return true;
            }
            catch (Exception ex)
            {
                CodeSite.Send("AdoMSSQL.cs OpenDB:", ex);
                return false;
            }
        }

        public Image read_img(string sql, string col)
        {
            //string sql = "select * from " + tab + " where id=" + id;
            DataSet ds = sel1(sql);
            if (ds_count == 0)
                return null;
            try
            {
                Byte[] Bytes = (Byte[])ds.Tables[0].Rows[0][col];
                MemoryStream mStream = new MemoryStream();
                mStream.Write(Bytes, 0, Bytes.Length);
                Image Img = Image.FromStream(mStream);
                return Img;
            }
            catch (System.Exception ex)
            {
                CodeSite.Send("AdoMSSQL.cs read_img:", ex);
                return null;
            }
        }

        public void save_img(string tab, string par1, string col, Image img)
        {
            MemoryStream stream = new MemoryStream();
            img.Save(stream, ImageFormat.Png);
            Byte[] bytes = stream.ToArray();
            String sql = "update " + tab + " set " + col + " = @bytes " + par1;
            cm = new SqlCommand(sql, cn);
            cm.Parameters.Add("@bytes", SqlDbType.Image).Value = bytes;
            cm.ExecuteNonQuery();
        }

        /// <summary>
        /// 执行查询语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet sel1(String sql)
        {
            try
            {
                da = new SqlDataAdapter(sql, cn);
                ds = new DataSet();

                da.Fill(ds);
                //da.FillSchema(ds, SchemaType.Mapped);
                if (ds == null || ds.Tables[0].Rows.Count == 0)
                {
                    ds_count = 0;
                }
                SqlCommandBuilder sqlCb = new SqlCommandBuilder(da);
                ds_count = ds.Tables[0].Rows.Count;
                return ds;

            }
            catch (Exception ex)
            {
                CodeSite.Send(sql + "; " + ex.ToString());
                CodeSite.Send("AdoMSSQL.cs sel1:", ex);
                ds_count = 0;
                return null;
            }
        }

        public void setParsCount(int i_count)
        {
            pars = new SqlParameter[i_count];
            for (int i = 0; i < i_count; i++)
            {
                pars[i] = new SqlParameter();
            }
        }

        public Boolean update()
        {
            try
            {
                da.Update(ds);
                return true;
            }
            catch (System.Exception ex)
            {
                CodeSite.Send(ex.ToString());
                CodeSite.Send("更新数据是发生错误", ex);
                return false;
            }
        }

        #endregion Methods
    }
}